The Business Master (3rd Edition)

home *** CD-ROM | disk | FTP | other *** search

/ The Business Master (3rd Edition) / The Business Master (3rd Edition).iso / files / spreotus / 123tech / 123prep.doc < prev next >

Wrap

Text File | 1984-08-26 | 14.0 KB | 274 lines

123PREP File Formatting Program Release 2.2 ==================================================================== WHAT IT DOES: 123PREP is a compiled basic program designed to convert a simple text (ASCII) file of FIXED LENGTH records into columns directly usable by the LOTUS 1-2-3 FILE IMPORT NUMBERS command. WHAT LOTUS LEFT OUT: Although 1-2-3 is very flexible about reading in a text file (using the FILE IMPORT TEXT command), it normally will read each line as one continuous line of text. That means that the spreadsheet can only be manipulated using complete lines: sorting, column manipulation, database operations and many other useful features of Lotus are out of reach. Numeric fields, despite their initial appearance, are considered text, which means that no mathematical operations can be performed. 1-2-3 does provide for importing a column-oriented file with the FILE IMPORT NUMBERS command, but that requires all text fields to be delimited with quotation marks and all numeric fields to be delimited with spaces. Problem is, Lotus doesn't provide a way to insert these characters. FOR EXAMPLE: A file list from a bulletin board (a really great use for this program) may look like this: 123PREP.EXE 05-27-84 REFORMATS TEXT FILES FOR LOTUS 130644 123PREP.DOC 05-27-84 DOCFILE FOR 123PREP.EXE 1 5130 What 1-2-3 will give you is normally all stuffed into column A: "123PREP.EXE 05-27-84 REFORMATS TEXT FILES FOR LOTUS 130644" "123PREP.DOC 05-27-84 DOCFILE FOR 123PREP.EXE 1 5130" What you want for your spreadsheet is a sortable database that you can mash around, probably requiring your file records to look like this, with column a containing "123prep.exe", and so on: a.......... b.... c. d.............................. e f.... "123PREP.EXE""05-27""84""REFORMATS TEXT FILES FOR LOTUS " 1 30644 "123PREP.DOC""05-27""84""DOCFILE FOR 123PREP.EXE " 1 5130 That's what 123PREP does: it allows you to vertically divide your file into fields and quickly create a properly formatted input file for 1-2-3's IMPORT feature, with numeric values preserved and fields correctly split into separate columns. Since the file is sliced vertically into fields, records should line up vertically. But don't worry about a few lines of junk if most of the file is usable -- take what's available and edit the rest in LOTUS. 123PREP is pretty forgiving in this respect. Page 2 123PREP File Formatting Program ==================================================================== PROGRAM LIMITS: This release supports drives A through D and file sizes up to the capacity of your disk drives. The amount of memory you have in your PC determines how big a file can be read into LOTUS. Records can now be up to about 200 characters wide and split into as many as 26 columns. For best performance, use different drives for your input and output files. A RAM disk makes this program really hum, because of all the disk activity implicit in the process (read a record, write a record). HOW TO USE IT: The program operates in 5 steps: 1. Select a TEXT file from any drive. IRMA.TXT is assumed if you don't enter a filename, but you MUST specify a disk drive. Press HOME for help or ESC to exit the program. At all subsequent screens, ESC "backs up" to the preceding screen to allow you to restart that section. For hard disk users: path names are not supported. 2. Select a disk drive and a file name to receive your formatted output. A disk drive must be specified. 123PREP.PRN is assumed as a default filename if you just press enter. Since the file will only be used for a short time, any name will do, but it MUST have .PRN as a file type for LOTUS to recognize it from the FILE IMPORT menu. 3. The first five records from your input file are then displayed on the screen. Each field you specify will become a separate column when you load the finished output file into LOTUS 1-2-3. A line of '^^^' (carat) characters is harmless and means that the record selected is blank; press PgDn to select another if you want. Using the cursor control keys described below (all of which are located on the number pad on the right side of your IBM/Compaq keyboard), select a sample record to serve as a template and then divide the template into fields. Press: Operation: ------------ --------------------------------------------------- HOME -- Redisplay the template marking HELP screen ESC -- Return to the output file selection screen Cursor Right -- Include this character in the current field Cursor Left -- Back space one character PgDn -- Select the next record as a template PgUp -- Select the previous record as a template END -- Restart the template marking screen Grey+ -- Accept the current field and start the next field Grey- -- Back up to the start of the previous field RETURN -- ALL DONE. Proceed to the next screen Page 3 123PREP File Formatting Program ==================================================================== For example, pressing the Cursor Right key (the "6" key on the number pad on the right-hand side of your keyboard) moves the cursor to the right and adds the current character to the field you're creating at the moment. Cursor Left backs up a space and drops that character from the current field. Typically, you'll use PgDn to select a record, Cursor Right to mark off the first field (you'll see small a's appear on the template), the Grey+ key to start the next field, and ENTER when you're all finished. If your records contain unneeded columns, assign them to separate fields and delete the column during the next step. 4. The next step looks at your template record and checks each field to see if it has a numeric value. If so, it is highlighted and will be output as a number surrounded by spaces in your file. If not, it will be output as text and surrounded with quote marks ("). If you may want an apparently numeric field to be treated as text, or vice versa, enter the field's letter to toggle it back and forth. You can also exclude a field from your output by pressing either SHIFT key and entering its letter. See ENHANCEMENTS below. 5. Process your files. Records from your input file are read in, processed and written to your output file, one at a time. Any double quote characters found are replaced with single quote characters during processing. If needed, you can append a record number at the end of each line to simplify editing in Lotus. Operation is continuous until the last input record is handled. Then start up LOTUS and enter /FIN, select your PRN file and press enter to import your new file. ERROR HANDLING has been addressed as completely as possible, but nobody's perfect. These are the errors that are most likely to occur in 123PREP and their corresponding error codes from the IBM Basic manual: 53 The drive or filename you specified for input doesn't exist. 55 Input and Output files can't have the same name. The program reads from one file and writes to a second file. 61 Your output disk is full. Try another one. 64 The filename is invalid. 70 Your diskette is write protected. 71 The disk drive you want isn't ready. 76 The drive or filename you specified for input doesn't exist. Page 4 123PREP File Formatting Program ==================================================================== PROBLEM REPORTING: For reference and problem reporting (in case you see a program related error code), the error screen displays the error number that occurred and in which line of my source program the error occurred. Nothing magic: you can look up the error code in your IBM BASIC manual. Please call me at the number shown below if you find a program "bug" so I can repair it. If you don't call, it won't get fixed and others may have the same problem. I really will try to help you. You can help both of us if you can show me how to duplicate the problem and provide the error message reported by the program. "ERRORS" IN YOUR FILE: Two special data errors you should be aware of when you use the Lotus IMPORT function -- 1. If your input file contains double quotation marks, Lotus insists that a new column has been started, whether you use 123PREP or not. Since you often can't control what your input file contains, the result is extra columns you don't want. This release of 123PREP arbitrarily deals with the problem by replacing any quote marks found in your input file with single quotes (apostrophes) during processing. Lotus sees single quotes as just an ordinary text character; most often, single quotes preserve the grammatical content of your records. 2. If you specify a numeric field and an "empty" column occurs, you need a "place-marker" so that Lotus doesn't ignore the field. Normally, spaces between numeric fields are ignored during IMPORT. As a result, the line "collapses" toward the left margin, shifting all subsequent columns to the left. This release looks for this condition during final processing and writes out zero to keep the columns lined up properly. Your INPUT file is never changed, so don't worry about damaging your original file. If either condition does occur, the exit screen will indicate it so you're aware that your output file has been modified. See your LOTUS documentation for a fuller discussion of the FILE IMPORT NUMBERS command. LOTUS 1-2-3 is a copyrighted product of Lotus Development Corp. DISTRIBUTION: 123PREP.EXE AND ITS DOCUMENTATION MAY BE FREELY COPIED AND SHARED WITH OTHER USERS FOR ANY NON-COMMERCIAL PURPOSE, BUT ANY COMMERCIAL USE OR PUBLICATION IS PROHIBITED WITHOUT MY WRITTEN PERMISSION. I will gladly supply the compiled program and this documentation file. Send a blank diskette, a return mailing label, and return postage in a mailer to the address below. To ensure that I can support you properly if you have problems, I will not distribute the source code. Page 5 123PREP File Formatting Program ==================================================================== ENHANCEMENTS IN RELEASE 2.2: As use of a program spreads, user suggestions surface that either directly or indirectly get plowed back into the next release. For those who called, thanks. New features built into this version of 123PREP include: 1. Quotation marks included in your INPUT file are automatically swapped out for apostrophes. Lotus uses quote marks to separate columns during the FILE IMPORT process. Similarly, if a numeric field evaluates to a zero value, a zero is written out to serve as a "place-marker" for Lotus. Otherwise, Lotus would "collapse" the column during IMPORT. The exit screen will tell you whether either condition occurred so that you know your file content has been changed. (Judy Epstein, Highland Park, Illinois.) 2. You can now exclude one or more columns from your OUTPUT file during final processing to squeeze the "air" out and simplify editing in LOTUS. Unneeded columns are selected during the TEXT/NUMERIC selection process by using either shift key and the letter that specifies the field. SHIFT-A, for example, tells the program to drop column A during final processing. It toggles, so doing it again restores the column. (Mike Erdmann, Minneapolis, Minnesota.) 3. Records can now be numbered during output if desired. No matter how you sort things around once you start up Lotus, you can always restore the original sequence of the file. Serves as an automatic DATA FILL feature. (Cheryl Wengroff, Skokie, Illinois.) 4. Fields I and Q no longer fail to clear the screen during the TEXT/NUMERIC selection process. (Mike Erdmann.) 5. Two limits were raised. You can now specify up to 26 fields and handle wider records. I have processed test records up to about 200 characters wide; looks ugly, but it seems to work. 6. A fourth HELP screen has been added to the TEXT/NUMERIC screen to explain the expanded options available there. 7. After you have finished processing, you're now offered a loop back to the top of the program to work on another file, instead of automatically exiting to DOS. Suggestions are welcome. . . ==================================================================== Jon Sims 123PREP.EXE 600-C South Boulevard Release 2.2 Evanston, Illinois May 27, 1984 Telephone: (312) 982-7312 (c) Copyright 1984 ====================================================================